﻿Imports System.Data.OleDb

Public Class MyProfile
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        '''''''''''''''''''''''''
        'The following statement is to set user role. If the user just registe, and they do not have a role, when they click 'My Profile',
        'the system will set user a role 
        Dim sqlGetRole As String = "SELECT * FROM aspnet_UsersInRoles WHERE UserId = @f01"
        Dim connGetRole As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("SpiritConnString").ConnectionString)
        Dim cmdGetRole As OleDbCommand = New OleDbCommand(sqlGetRole, connGetRole)

        connGetRole.Open()
        cmdGetRole.Parameters.AddWithValue("@f01", Membership.GetUser(User.Identity.Name).ProviderUserKey)

        Dim dbGetRoleReader = cmdGetRole.ExecuteReader()

        dbGetRoleReader.Read()

        If Not dbGetRoleReader.HasRows Then     'If the user's information is not in the table
            Dim sqlSetRole As String = "INSERT INTO aspnet_UsersInRoles (UserId, RoleId) VALUES (@f02, @f03)"
            Dim connSetRole As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("SpiritConnString").ConnectionString)
            Dim cmdSetRole As OleDbCommand = New OleDbCommand(sqlSetRole, connSetRole)

            connSetRole.Open()
            cmdSetRole.Parameters.AddWithValue("@f02", Membership.GetUser(User.Identity.Name).ProviderUserKey)
            cmdSetRole.Parameters.AddWithValue("@f03", "2")     'Set user role as 'basic user
            cmdSetRole.ExecuteNonQuery()

            connSetRole.Close()
        End If

        connGetRole.Close()


        '''''''''''''''''''''''''''''''''''''''''''''
        'The following statement is to set user as the 'Super User. The Super User can see the list of all users.
        'To become a super user, the user has to upload a spirit. 
        Dim sqlChangeRole As String = "SELECT * FROM SpiritList WHERE diner_fkid=@f1"
        Dim connChangeRole As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("SpiritConnString").ConnectionString)
        Dim cmdChangeRole As OleDbCommand = New OleDbCommand(sqlChangeRole, connChangeRole)

        connChangeRole.Open()
        cmdChangeRole.Parameters.AddWithValue("@f1", User.Identity.Name)

        Dim dbChangeRoleReader = cmdChangeRole.ExecuteReader()

        dbChangeRoleReader.Read()

        If dbChangeRoleReader.HasRows Then      'If the user has uploaded a spirit, the systeme will set them as the 'Super User'
            Dim sqlUpdateRole As String = "UPDATE aspnet_UsersInRoles SET RoleId=@f11 WHERE UserId = @f22"
            Dim connUpdateRole As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("SpiritConnString").ConnectionString)
            Dim cmdUpdateRole As OleDbCommand = New OleDbCommand(sqlUpdateRole, connUpdateRole)

            connUpdateRole.Open()
            cmdUpdateRole.Parameters.AddWithValue("@f11", "1")
            cmdUpdateRole.Parameters.AddWithValue("@f22", Membership.GetUser(User.Identity.Name).ProviderUserKey)
            cmdUpdateRole.ExecuteNonQuery()

            connUpdateRole.Close()
        End If

        connChangeRole.Close()


        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        'The following statement is to check user's role. If the user is the basic user, 
        'they cannot see the Label of 'View All Other User'. If they are super user, they can.
        Dim sql As String = "SELECT UserId, RoleId FROM aspnet_UsersInRoles WHERE UserId=@f111"        'This is an SQL statment
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("SpiritConnString").ConnectionString)       'Connect to the Database
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)

        conn.Open()
        cmd.Parameters.AddWithValue("@f111", Membership.GetUser(User.Identity.Name).ProviderUserKey)

        Dim dbReader = cmd.ExecuteReader()

        dbReader.Read()

        If dbReader("RoleId") = "2" Then
            lb_userRole.Text = "Basic User"
            hpl_otherUsers.Visible = False
        ElseIf dbReader("RoleID") = "1" Then
            lb_userRole.Text = "Super User"
            hpl_otherUsers.Visible = True
        End If

        conn.Close()

    End Sub

End Class